# INSERT INTO `va_stratlab`.`sdfg` (`id`, `symbol`, `date`, `bool1`, `bool2`, `dec1`)
# VALUES (NULL, 'MSFT', '2012-01-01', '1', '0', '10,4');
# fills values of stock status xts object into newly created VadbTable
fillVadbTable <- function(con, tblname, symname, SSTS) {
  sqlcmd <- function(row) {
    sql <- sprintf("INSERT INTO `va_stratlab`.`%s` 
                   (`id`, `symbol`, `date`,%s) VALUES 
                   (NULL, '%s', ",tblname,cnamesstring,symname)
    dates <- sprintf("'%s',",index(row))
    vals <- paste(sprintf("%f",row), collapse=",")
    sql <- paste(sql, dates, vals, ")")
    return(sql)
  }
  dimSSTS <- dim(SSTS)
  cnames <- colnames(SSTS)
  lcnames <- length(cnames)
  cnamesstring <- paste(paste(sprintf("`b_%s`",cnames[1:(lcnames-6)]), collapse=","),
                        paste(sprintf("`d_%s`",cnames[(lcnames-5):lcnames]), collapse=","), sep=",")
  for (i in 1:dimSSTS[1]) {
    dbSendQuery(con, sqlcmd(SSTS[i,]))
  }
}



# bcols contain the identifier of the boolean columns
# bcolsv contain the boolean values
fillVadbTable_an <- function(con, tblname, id, obs, bcols, bcolsv, 
                             c0_BSSummary, c0_SSSummary,
                             o1_BSSummary, o1_SSSummary,
                             c1_BSSummary, c1_SSSummary) {
  decimalcols <- c("1stQ", "Median", "Mean", "3rdQ")
  sqlcmd <- function() {
    sql <- sprintf("INSERT INTO `va_stratlab`.`%s_an`
                   (`id`, `obs`, %s , %s , %s , %s , %s , %s , %s ) VALUES 
                   (%d, %d, %s , %s , %s , %s , %s , %s , %s)",
                   tblname, bcols, c0_dbcols, c0_dscols, o1_dbcols, o1_dscols, c1_dbcols, c1_dscols,
                   id, obs, bvals, c0_BSvals, c0_SSvals, o1_BSvals, o1_SSvals, c1_BSvals, c1_SSvals)
    return(sql)
  }
  # form the column names
  bcols <- paste(sprintf("`%s`",bcols), collapse=",")
  c0_dbcols <- paste(sprintf("`d_c0_b_%s`",decimalcols), collapse=",")
  c0_dscols <- paste(sprintf("`d_c0_s_%s`",decimalcols), collapse=",") 
  o1_dbcols <- paste(sprintf("`d_o1_b_%s`",decimalcols), collapse=",")
  o1_dscols <- paste(sprintf("`d_o1_s_%s`",decimalcols), collapse=",")
  c1_dbcols <- paste(sprintf("`d_c1_b_%s`",decimalcols), collapse=",")
  c1_dscols <- paste(sprintf("`d_c1_s_%s`",decimalcols), collapse=",")  
  
  # now form the values
  bvals <- paste(sprintf("'%d'",bcolsv), collapse=",")
  # omit Min and Max of Summary
  c0_BSvals <- paste(sprintf("'%f'",c0_BSSummary[2:5]), collapse=",")
  c0_SSvals <- paste(sprintf("'%f'",c0_SSSummary[2:5]), collapse=",")  
  o1_BSvals <- paste(sprintf("'%f'",o1_BSSummary[2:5]), collapse=",")
  o1_SSvals <- paste(sprintf("'%f'",o1_SSSummary[2:5]), collapse=",")
  c1_BSvals <- paste(sprintf("'%f'",c1_BSSummary[2:5]), collapse=",")
  c1_SSvals <- paste(sprintf("'%f'",c1_SSSummary[2:5]), collapse=",")
  #return(sqlcmd())
  dbSendQuery(con, sqlcmd())
}